using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace ZFrameWork.Util
{
    public static class ExcelTool
    {
        public static DataSet GetDataSetFromExcel(string excelFileName, string sheetName, out string msg)
        {
            msg = string.Empty;

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFileName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            OleDbConnection conn = new OleDbConnection(strConn);
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = new DataSet();
            strExcel = string.Format("select * from [{0}$]", sheetName);

            try
            {
                conn.Open();
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(ds, "dtSource");
                return ds;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return ds;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

        public static void SaveToExcel(string fileName,DataSet ds)
        {
            if (ds != null && ds.Tables.Count > 0)
            {
                string titleTR = "<tr>";

                List<string> ColumnNames = new List<string>();

                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    string theName = ds.Tables[0].Columns[i].ColumnName;
                    ColumnNames.Add(theName);
                    titleTR += string.Format("<td>{0}</td>", theName);
                }

                titleTR += "</tr>";

                StringBuilder sbContent = new StringBuilder();
 
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    sbContent.Append("<tr>");
                    foreach (string columnName in ColumnNames)
                    {
                        if (dr[columnName] != System.DBNull.Value)
                            sbContent.Append(string.Format("<td>{0}</td>", dr[columnName].ToString()));
                        else
                            sbContent.Append(string.Format("<td>{0}</td>", string.Empty));
                    }
                    sbContent.Append("</tr>");
                }

                string dataHTML = string.Format("<table>{0}{1}</table>", titleTR, sbContent.ToString());

                FileTools.WriteToFile(fileName, dataHTML);
            }
        }
    }
}
